# coding: utf-8
# 用来创建数据库模型
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
import sys
from sqlalchemy import or_, and_, between

app = Flask(__name__)
app.config.from_pyfile('/data/mngsSYS/b/app/config.py')
db = SQLAlchemy(app)

class SampleQstat(db.Model):
    __tabname__ = 'sampleqstat'
    id = db.Column(db.Integer, primary_key=True)
    location = db.Column(db.String(4))
    jobid = db.Column(db.String(64), index=True)
    sample = db.Column(db.String(32), index=True)
    type = db.Column(db.String(32))
    autosomeavg = db.Column(db.String(8))
    autosomemed = db.Column(db.String(8))
    x = db.Column(db.String(8))
    y = db.Column(db.String(8))
    prog = db.Column(db.String(4))
    table = db.Column(db.String(4))
    allreads = db.Column(db.String(16))
    humanreads = db.Column(db.String(16))
    humanrate = db.Column(db.String(8))
    nhuamnreads = db.Column(db.String(16))
    nhumanrate = db.Column(db.String(8))
    nhdupreads = db.Column(db.String(16))
    nhduprate = db.Column(db.String(8))
    q30 = db.Column(db.String(8))
    primersreads = db.Column(db.String(16))
    primersrate = db.Column(db.String(8))
    plasmidreads = db.Column(db.String(16))
    plasmidrate = db.Column(db.String(8))
    unclassifyreads = db.Column(db.String(16))
    unclassifyrate = db.Column(db.String(8))
    addtime = db.Column(db.DateTime, default=datetime.now)

    def update(self, **kwargs):
        for k, v in kwargs.items():
            setattr(self, k, v)

if __name__ == '__main__':
    loc = {'guangzhou':'广州', 'shanghai':'上海', 'fuzhou':'福州', 'ZZ':'郑州', 'changsha':'长沙', 'beijing':'北京','zhengzhou':'郑州','GZ':'广州'}
    location = loc[sys.argv[1].split('/')[-3]]
    with open(sys.argv[1], encoding='gbk') as infile:
        infile.readline()
        try:
            for i in infile:
                s, _, t, avg, med, x, y, p, tb, ar, hr, hrr, nhr, nhrr, nhd, nhdr, q, mirco, prr, prra, plr, plra, ur, ura, total_base = i.split('\t')
                #s, _, t, avg, med, x, y, p, tb, ar, hr, hrr, nhr, nhrr, nhd, nhdr, q, mirco, prr, prra, plr, plra, ur, ura=i.split('\t')
                tmp = SampleQstat.query.filter(and_(SampleQstat.jobid==sys.argv[2], SampleQstat.sample==s)).first()
                if tmp:
                    tmp.update(jobid=sys.argv[2], location=location, sample=s, type=t, autosomeavg=avg, autosomemed=med, x=x, y=y, \
                    prog=p, table=tb, allreads=ar, humanreads=hr, humanrate=hrr, nhuamnreads=nhr, nhumanrate=nhrr, nhdupreads=nhd, \
                    nhduprate=nhdr, q30=q, primersreads=prr, primersrate=prra, plasmidreads=plr, plasmidrate=plra, unclassifyreads=ur, unclassifyrate=ura)
                else:
                    sq = SampleQstat(jobid=sys.argv[2], location=location, sample=s, type=t, autosomeavg=avg, autosomemed=med, x=x, y=y, \
                        prog=p, table=tb, allreads=ar, humanreads=hr, humanrate=hrr, nhuamnreads=nhr, nhumanrate=nhrr, nhdupreads=nhd, \
                        nhduprate=nhdr, q30=q, primersreads=prr, primersrate=prra, plasmidreads=plr, plasmidrate=plra, unclassifyreads=ur, unclassifyrate=ura)
                    db.session.add(sq)
            db.session.commit()
        except Exception as e:
            print(f"{e}")
            db.session.rollback()
            exit('任务样本导入出错！数据库回滚完毕！')

